
/**
 * 生成SQL查询语句
 */
Ext.define('SqlBuilder2', {
	extend : 'Ext.window.Window',
	layout : 'border',
	modal : true,
	maximizable : true,
	maximized: true,
	title : 'SQL生成器',
	iconCls : 'btn-pred',
	height : 700,
	width : 1000,
	buttonAlign : 'center',

	initComponent : function() {
		var me = this;
		this.columnData = [];
		this.tableData = [];
		this.columnStore = Ext.create('Ext.data.ArrayStore', {
					autoLoad : true,
					fields : ['oid', 'code', 'name']
				});
		this.tableStore = Ext.create('Ext.data.ArrayStore', {
					autoLoad : true,
					fields : ['oid', 'code', 'name']
				});
		// 数据表
		var topbar1 = new Ext.Toolbar({
					items : [{
								iconCls : 'btn-add',
								text : '添加表',
								xtype : 'button',
								scope : this,
								handler : this.addTable
							}, {
								iconCls : 'btn-del',
								text : '删除表',
								xtype : 'button',
								scope : this,
								handler : this.delTable
							}]
				});
		this.tableGrid = new KE.GridPanel({
					region : 'north',
					height : 200,
					title : '数据表',
					tbar : topbar1,
					id : 'SqlTableGrid',
					selModel : 'rowmodel',
					showPaging : false,
					pageSize : 99999,
					hideHeaders : true,
					store : this.tableStore,
					columns : [{
								header : '中文名',
								dataIndex : 'name',
								width: 150
							}, {
								header : '表名',
								dataIndex : 'code'
							}]
				});
		// 输出
		this.outPanel = new Ext.panel.Panel({
					region : 'center',
					title : '结果输出设置',
					tbar: {
						items : [{
							iconCls : 'btn-add',
							text : '设置输出字段',
							xtype : 'button',
							scope : this,
							handler : this.outSetting							
						}]
					},
					bodyStyle : 'padding: 10px',
					layout: 'fit',
					items : [{
						name: 'outsetting',
						emptyText: '默认：统计记录  select count(*)',
						xtype : 'textarea'
					}]
				});
		var westPanel = new Ext.panel.Panel({
					region : 'west',
					width : 320,
					layout : 'border',
					items : [this.tableGrid, this.outPanel]
				});

		// 表关系
		var topbar5 = new Ext.Toolbar({
					items : [{
								iconCls : 'btn-add',
								text : '添加关系',
								xtype : 'button',
								scope : this,
								handler : this.addRelation
							}, {
								iconCls : 'btn-del',
								text : '删除关系',
								xtype : 'button',
								scope : this,
								handler : this.delRelation
							}]
				});
		this.relationPanel = new KE.GridPanel({
					region : 'north',
					height : 200,
					title : '设置表间关系<font color="red">（单表时不需要设置）</font>',
					tbar : topbar5,
					showPaging : false,
					pageSize : 99999,
					store: new Ext.data.ArrayStore({
						autoLoad : true,
						fields : ['oid', 'oid2', 'col', 'col2', 'table1', 'column1', 'eq', 'table2', 'column2']
					}),
					plugins : [new Ext.grid.plugin.CellEditing({
								clicksToEdit : 1
							})],
					columns : [{
						dataIndex : 'oid',
						hidden : true						
					}, {
						dataIndex : 'oid2',
						hidden : true
					}, {
						dataIndex : 'col',
						hidden : true		
					}, {
						dataIndex : 'col2',
						hidden : true		
					}, {
						header : '表名1',
						dataIndex : 'table1',
						editor : new Ext.form.ComboBox({
									allowBlank : false,
									valueField : 'name',
									displayField : 'name',
									editable : false,
									lazyInit : true,
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',
									store : me.tableStore,
									listeners : {  
			                            select : function(combo, record) {
			                            	var selRs = me.relationPanel.getSelection()[0];
			                            	selRs.data.oid = record.data.oid;
			                            	selRs.data.column1 = ''; // 清空列名1
			                            } 
			                        }  
								})
					}, {
						header : '列名1',
						dataIndex : 'column1',
						editor : new Ext.form.ComboBox({
									allowBlank : false,
									valueField : 'name',
									displayField : 'name',
									editable : false,
									lazyInit : true,
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',	
									store : me.columnStore,
									listeners : {  
			                            beforequery : function(queryPlan) {
			                            	var selRs = me.relationPanel.getSelection();
			                            	var tabId = selRs[0].data.oid;
			                                queryPlan.combo.store.loadData(me.columnData[tabId]);
			                            },
			                            select : function(combo, record) {
			                            	var selRs = me.relationPanel.getSelection()[0];
			                            	selRs.data.col = record.data.code;
			                            } 
			                        }  
								})
					}, {
						dataIndex : 'eq',
						width : 30
					}, {
						header : '表名2',
						dataIndex : 'table2',
						editor : new Ext.form.ComboBox({
									allowBlank : false,
									valueField : 'name',
									displayField : 'name',
									editable : false,
									lazyInit : true,
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',
									store : me.tableStore,
									listeners : {  
			                            select : function(combo, record) {
			                            	var selRs = me.relationPanel.getSelection()[0];
			                            	selRs.data.oid2 = record.data.oid;
			                            	selRs.data.column2 = ''; // 清空列名2
			                            } 
			                        }  
								})
					}, {
						header : '列名2',
						dataIndex : 'column2',
						editor : new Ext.form.ComboBox({
									allowBlank : false,
									valueField : 'name',
									displayField : 'name',
									editable : false,
									lazyInit : true,
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',	
									store : me.columnStore,
									listeners : {  
			                            beforequery : function(queryPlan) {
			                            	var selRs = me.relationPanel.getSelection();
			                            	var tabId = selRs[0].data.oid2;
			                                queryPlan.combo.store.loadData(me.columnData[tabId]);
			                            },
			                            select : function(combo, record) {
			                            	var selRs = me.relationPanel.getSelection()[0];
			                            	selRs.data.col2 = record.data.code;
			                            }  
			                        }  
								})
					}]
				});
		// 查询条件
		var topbar2 = new Ext.Toolbar({
					items : [{
								iconCls : 'btn-add',
								text : '添加条件',
								xtype : 'button',
								scope : this,
								handler : this.addQuery
							}, {
								iconCls : 'btn-del',
								text : '删除条件',
								xtype : 'button',
								scope : this,
								handler : this.delQuery
							}]
				});
		this.centerPanel = new KE.GridPanel({
					region : 'center',
					tbar : topbar2,
					title : '查询条件设置',
					id : 'SqlCenterGrid',
					showPaging : false,
					pageSize : 99999,
					store: new Ext.data.ArrayStore({
						autoLoad : true,
						fields : ['oid', 'gx', 'zkh', 'tab', 'col', 'opt', 'val', 'ykh', 'code']
					}),
					plugins : [new Ext.grid.plugin.CellEditing({
								clicksToEdit : 1
							})],
					columns : [{
								dataIndex : 'oid',
								hidden : true						
							}, {
								dataIndex : 'code',
								hidden : true						
							}, {
								header : '关系',
								dataIndex : 'gx',
								editor : new Ext.form.ComboBox({
									allowBlank : false,									
									editable : false,									
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',	
									store : [['并且', '并且'], ['或者', '或者']]
								})
							}, {
								header : '左括号',
								dataIndex : 'zkh',
								width: 80,
								editor : new Ext.form.ComboBox({
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',	
									store : [['(', '('], ['( (', '( ('], ['( ( (', '( ( ('], ['( ( ( (', '( ( ( (']]
								})
							}, {
								header : '表名',
								dataIndex : 'tab',
								editor : new Ext.form.ComboBox({
									allowBlank : false,
									valueField : 'name',
									displayField : 'name',
									editable : false,
									lazyInit : true,
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',
									store : me.tableStore,
									listeners : {  
			                            select : function(combo, record) {
			                            	var selRs = me.centerPanel.getSelection()[0];
			                            	selRs.data.oid = record.data.oid;
			                            	selRs.data.col = ''; // 清空列名
			                            } 
			                        }  
								})
							}, {
								header : '列名',
								dataIndex : 'col',
								editor : new Ext.form.ComboBox({
									allowBlank : false,
									valueField : 'name',
									displayField : 'name',
									editable : false,
									lazyInit : true,
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',	
									store : me.columnStore,
									listeners : {  
			                            beforequery : function(queryPlan) {
			                            	var selRs = me.centerPanel.getSelection()[0];
			                            	var tabId = selRs.data.oid;
			                                queryPlan.combo.store.loadData(me.columnData[tabId]);
			                            },
			                            select : function(combo, record) {
			                            	var selRs = me.centerPanel.getSelection()[0];
			                            	selRs.data.code = record.data.code;
			                            }  
			                        }  
								})
							}, {
								header : '运算符',
								dataIndex : 'opt',
								editor : new Ext.form.ComboBox({
									forceSelection : true,
									triggerAction : 'all',
									editable: false,	
									queryMode : 'local',	
									store : [['=', '='], ['<>', '<>'], ['>', '>'], 
										['>=', '>='], ['<', '<'], ['<=', '<='],
										['LIKE', 'LIKE'], ['IN', 'IN'], ['IS NULL', 'IS NULL'], 
										['IS NOT NULL', 'IS NOT NULL']],
									listeners : {  
			                            select : function(combo, record) {
			                            	var selRs = me.centerPanel.getSelection()[0];
			                            	if(record.data.field1=='IS NULL' || record.data.field1=='IS NOT NULL') {
			                            		selRs.data.val = '';
			                            	}
			                            }  
			                        }  
								})
							}, {
								header : '值',
								dataIndex : 'val',
								editor : new Ext.form.ComboBox({
									store : [['{当前用户}','{当前用户}'], ['{当前用户单位}','{当前用户单位}'], 
										['{考核民警}','{考核民警}'], ['{考核民警单位}','{考核民警单位}'], 
										['{考核开始时间}','{考核开始时间}'], ['{考核结束时间}','{考核结束时间}']]
								})
							}, {
								header : '右括号',
								dataIndex : 'ykh',
								width: 80,
								editor : new Ext.form.ComboBox({
									forceSelection : true,
									triggerAction : 'all',
									queryMode : 'local',	
									store : [[')', ')'], [') )', ') )'], [') ) )', ') ) )'], [') ) ) )', ') ) ) )']]
								})
							}]
				});
		var mainPanel = new Ext.panel.Panel({
					region : 'center',
					layout : 'border',
					items : [this.relationPanel, this.centerPanel]
				});

		var panel1 = new Ext.panel.Panel({
					region : 'center',
					layout : 'border',
					items : [westPanel, mainPanel]
				});
		this.panel2 = new Ext.panel.Panel({
					region : 'south',
					title : 'SQL输出窗口',
					height : 180,
					layout : 'fit',
					bodyStyle : 'padding: 10px',
					items : [{
								readOnly: true,
								allowBlank: false,
								name: 'output',
								xtype : 'textarea'
							}]
				});
		// 1、选择表
		this.items = [panel1, this.panel2];
		this.buttons = [{
					text : '生成SQL',
					iconCls : 'btn-save',
					scope : this,
					handler : this.generateSQL
				}, {
					text : '确定',
					iconCls : 'btn-ok',
					scope : this,
					handler : this.save
				}, {
					text : '关闭',
					iconCls : 'btn-cancel',
					scope : this,
					handler : this.cancel
				}];

		this.callParent();
	},
	// 确定
	save: function() {
		if(this.textarea) {
			var val = this.panel2.getCmpByName('output').getValue();
			this.textarea.setValue(val);
		}
		this.close();
	},
	// 取消
	cancel : function() {
		this.close();
	},
	addTable : function() {
		var me = this;
		AppTableSelector.getView(function(ids, codes, names) {
					ids = ids.split(',');
					codes = codes.split(',');
					names = names.split('，');
					var arr = [];
					for (var i = 0; i < ids.length; i++) {
						arr.push([ids[i], codes[i], names[i]]);
					}
					me.tableGrid.store.loadData(arr);
					me.tableData = arr;
					// ajax请求获得各表的字段信息
					$postAjax({
						async: true,
						url : __ctxPath + '/jxkh/listColumnAppTable.do',
						params: {
							ids: ids
						},
						callback: function(json) {
							me.columnData = json;
						}
					});
				}, false).show();
		this.panel2.getCmpByName('output').setValue('');
	},
	// 增加关系
	addRelation: function() {
		var rec = [{oid: '', oid2: '', table1: '', column1: '', eq: '=', table2: '', column2: ''}];
		var grid = this.relationPanel; 
		grid.store.insert(grid.store.getCount(), rec);
		this.panel2.getCmpByName('output').setValue('');
	},
	// 删除关系
	delRelation: function() {
		var selRs = this.relationPanel.getSelection();
		if(selRs.length == 0) {
			Ext.ux.Toast.msg('操作信息', '请选择要删除的记录');
			return;
		}
		this.relationPanel.store.remove(selRs);
		this.panel2.getCmpByName('output').setValue('');
	},
	// 增加查询
	addQuery: function() {
		var rec = [{oid: '', gx: '并且', zkh: '', tab: '', col: '', opt: '=', val: '', ykh: ''}];
		var grid = this.centerPanel; 
		grid.store.insert(grid.store.getCount(), rec);	
		this.panel2.getCmpByName('output').setValue('');
	},
	// 删除查询
	delQuery: function() {
		var selRs = this.centerPanel.getSelection();
		if(selRs.length == 0) {
			Ext.ux.Toast.msg('操作信息', '请选择要删除的记录');
			return;
		}
		this.centerPanel.store.remove(selRs);
		this.panel2.getCmpByName('output').setValue('');
	},
	// 设置输出字段
	outSetting: function() {
		new SqlBuilderOutSetting({
			tableData: this.tableData,
			columnData: this.columnData
		}).show();
		this.panel2.getCmpByName('output').setValue('');
	},
	// 生成SQL
	generateSQL: function() {
		var store1 = this.tableGrid.store;
		var cnt = store1.getCount();
		if(cnt == 0) {
			Ext.ux.Toast.msg('操作信息', '没有要操作的数据表');
			return;
		}
		var sql = '';
		var outsetting = this.outPanel.getCmpByName('outsetting').getValue();
		if(outsetting == '') {
			sql += 'SELECT count(*) FROM ';
		} else {
			// 查询sql
		}		
	
		// 加入：操作人和操作时间
		var czsql = ' WHERE ';
		for(var i=0; i<cnt; i++) {
			var data = store1.getAt(i).data;
			sql += data.code + ' tab' + data.oid + ", ";
			czsql += 'tab'+data.oid+'.CZR=\'{考核民警}\' and tab'+data.oid+'.CZSJ between \'{考核开始时间}\' and \'{考核结束时间}\' and '
		}
		sql = sql.substring(0, sql.length-2);
		sql += czsql.substring(0, czsql.length-4)
		
		// 表关系
		var store2 = this.relationPanel.store;
		cnt = store2.getCount();
		for(var i=0; i<cnt; i++) {
			var data = store2.getAt(i).data;
			sql += ' AND tab' + data.oid + '.' + data.col + ' = tab' + data.oid2 + '.' + data.col2;
		}
		
		// 查询
		var query = '';
		var store3 = this.centerPanel.store;
		cnt = store3.getCount();
		for(var i=0; i<cnt; i++) {
			var data = store3.getAt(i).data;
			var zkh = data.zkh ? data.zkh : '';
			var ykh = data.ykh ? data.ykh : '';
			var val = data.val ? data.val : '';
			if(data.opt == 'LIKE') {
				val = '\'%' + val + '\'';
			}
			if(val != '') {
				val = '\'' + val + '\'';
			}			
			query += ' '+data.gx+' ' + zkh + 'tab'+data.oid+'.'+data.code + ' '+data.opt + ' ' + val + ykh; 
		}
		// 进行替换
		query = query.replaceAll('并且', 'AND').replaceAll('或者', 'OR');
		sql = sql + query;
		if(sql.endsWith('WHERE 1=1')) {
			sql = sql.substring(0, sql.length-10);
		}
		sql = sql.replaceAll('1=1 AND', '').replaceAll('1=1 OR', '');
		
		// 输出SQL
		this.panel2.getCmpByName('output').setValue(sql);		
	}
	
});
